Excel 2013 keeps updating the pivot table data ranges to a prior named file.

I built out a spreadsheet with multiple tabs of pivot tables all referencing the master data tab in the same spreadsheet.

When I "save as" the document as a new name, the pivot tables refer back to the old document.  Not sure why this is happening and it is very frustrating and time consuming to update the data ranges each time I save the document with an update. I am just changing the file name with V2, V3, V4 etc or changing the file name to have a different date extension (20150522 to 20150529)

Is this a MS 2013 bug?  Is there any way to lock down the data reference fields to stay in the document and not try to refer externally?

I just changed from MS Office 2010 to 2013 and this problem started.

Thanks

Hi,

Based on your description, did you get any error messages when updating Pivot Table data? And if the table data and Pivot Table in the same Workbook?

I test it in my own environment, I find if the table data and Pivot Table in the same Workbook it works fine. But if the data of Pivot Table from external data I can get the error as shown in the following figure. Did you get the same error message?

If you are using excel 2013, when you rename or move your workbook, your PivotTable data source may change.

In excel, click on the PivotTable- ANALYZE- Change Data Source to check it.

A workaround: When you create the pivot table, check the option: Add this data to Data Model.

If my understanding is incorrect, please be a bit more precise explain your problem so that I can get more accurate solutions to this problem. And if you get any error messages please provide the whole error message.

Hope its helpful.

Regards,

George Zhao
TechNet Community Support

Thank you for your email.

To help clarify, the pivot tables are already built in an existing spreadsheet that I had been running for several months in Excel 2010.

Weekly, I would:

1) "Save As" a new name

2) Refresh the data on the master tab

3) Under the Data tab would click "Refresh All" and all the pivots would update (and update the charts based on them).

In Excel 2013, when I did the "Refresh All", I would get the following error:

[image blocked until my account can be verified]

"... The operation connects to an external data source. ..."

Going into each Pivot table: PivotTable- ANALYZE- Change Data Source, I would find it was
referring back to last weeks file name instead of the current file name:

 [image blocked until my account can be verified]

The full field:
"'[Status as of 20150529 Final.xlsx]Assigned Workflow by BP'!$A$8:$BW$1968"

Before the Save As, this field read simply:

"Assigned Workflow by BP'!$A$8:$BW$1968" (not a file name, as the tab was internal to the
worksheet).

My colleagues that are still on Excel 2010 don't have this issue when we tested.  The
ones on 2013 do. This issue started for me only when I switched to 2013.

I have updated the pivot data range to refer to the tab only in the worksheet and the "Use an
external data source" is not checked.  But when doing a "Save As", it refers back to the old name.

Regarding your quote: "If you are using excel 2013, when you rename or move your
workbook, your PivotTable data source may change."

Can you explain that a bit more and how to prevent that?  I am not referring to an
external data source and don't want my data source to change.

Is this additional info helpful?

Thanks

• Edited by MS Office USer 3 hours 26 minutes ago

Thank you for your email.

To help clarify, the pivot tables are already built in an existing spreadsheet that I had been running for several months in Excel 2010.

Weekly, I would:

1) "Save As" a new name

2) Refresh the data on the master tab

3) Under the Data tab would click "Refresh All" and all the pivots would update (and update the charts based on them).

In Excel 2013, when I did the "Refresh All", I would get the following error:

[image blocked until my account can be verified]

"... The operation connects to an external data source. ..."

Going into each Pivot table: PivotTable- ANALYZE- Change Data Source, I would find it was
referring back to last weeks file name instead of the current file name:

 [image blocked until my account can be verified]

The full field:
"'[Status as of 20150529 Final.xlsx]Assigned Workflow by BP'!$A$8:$BW$1968"

Before the Save As, this field read simply:

"Assigned Workflow by BP'!$A$8:$BW$1968" (not a file name, as the tab was internal to the
worksheet).

My colleagues that are still on Excel 2010 don't have this issue when we tested.  The
ones on 2013 do. This issue started for me only when I switched to 2013.

I have updated the pivot data range to refer to the tab only in the worksheet and the "Use an
external data source" is not checked.  But when doing a "Save As", it refers back to the old name.

Regarding your quote: "If you are using excel 2013, when you rename or move your
workbook, your PivotTable data source may change."

Can you explain that a bit more and how to prevent that?  I am not referring to an
external data source and don't want my data source to change.

Is this additional info helpful?

Thanks

• Edited by MS Office USer Thursday, June 04, 2015 4:04 AM

Thank you for your email.

To help clarify, the pivot tables are already built in an existing spreadsheet that I had been running for several months in Excel 2010.

Weekly, I would:

1) "Save As" a new name

2) Refresh the data on the master tab

3) Under the Data tab would click "Refresh All" and all the pivots would update (and update the charts based on them).

In Excel 2013, when I did the "Refresh All", I would get the following error:

[image blocked until my account can be verified]

"... The operation connects to an external data source. ..."

Going into each Pivot table: PivotTable- ANALYZE- Change Data Source, I would find it was
referring back to last weeks file name instead of the current file name:

 [image blocked until my account can be verified]

The full field:
"'[Status as of 20150529 Final.xlsx]Assigned Workflow by BP'!$A$8:$BW$1968"

Before the Save As, this field read simply:

"Assigned Workflow by BP'!$A$8:$BW$1968" (not a file name, as the tab was internal to the
worksheet).

My colleagues that are still on Excel 2010 don't have this issue when we tested.  The
ones on 2013 do. This issue started for me only when I switched to 2013.

I have updated the pivot data range to refer to the tab only in the worksheet and the "Use an
external data source" is not checked.  But when doing a "Save As", it refers back to the old name.

Regarding your quote: "If you are using excel 2013, when you rename or move your
workbook, your PivotTable data source may change."

Can you explain that a bit more and how to prevent that?  I am not referring to an
external data source and don't want my data source to change.

Is this additional info helpful?

Thanks

• Edited by MS Office USer Thursday, June 04, 2015 4:04 AM

Hi,

Based on your description, I suppose you issue is the path of data source in Pivot Table changes to absolute path so when you change the file name move the file the data source is wrong you cant refresh all data in Pivot Table Excel 2013.

As I know there is a workaround for you issue, save the file as *.xls file.

You can refer to this thread below and you can find this is a known issue in Excel 2013.

https://social.technet.microsoft.com/Forums/office/en-US/43bf5110-dfad-40e5-a71c-e9736da6fbc2/data-source-path-in-pivot-table-changes-to-absolute-on-its-own?forum=excel

If you wonder to know more information about absolute path and relative path in excel you can refer to this link:

https://support.microsoft.com/en-us/kb/328440/en-us

Hope its helpful.

Regards,

George Zhao
TechNet Community Support

Thank you George,

The reading was interesting.  I tried the solutions in the thread you provided, but nothing worked.

For new 2013 spreadsheets, it looks like I can use the following with some success.

However there does appear any way to tell Excel 2013 to keep existing pivot tables data source paths relative and internal to the workbook (basically not change it).  Is that correct?

This seems to be an error in design logic and is frustrating several people according to the forum link that you sent.  Will this be updated soon in 2013?

I am not looking forward to going back and rebuilding all my 2010 templates again in 2013 as I use them on new projects.  That is a lot of rework for the new version not to be compatible.

Thank you George,

The reading was interesting.  I tried the solutions in the thread you provided, but nothing worked.

For new 2013 spreadsheets, it looks like I can use the following with some success.

However there does not appear any way to tell Excel 2013 to keep an existing pivot tables data source paths relative and internal to the workbook (basically not change it).  Is that correct?

This seems to be an error in design logic and is frustrating several people according to the forum link that you sent.  Will this be updated soon in 2013?

I am not looking forward to going back and rebuilding all my 2010 templates again in 2013 as I use them on new projects.  That is a lot of rework for the new version not to be compatible.

• Edited by MS Office USer Tuesday, June 23, 2015 9:05 PM

Hi, 

This is still an issue in Excel 2016 Preview.......